﻿using SQLite;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Windows.UI.Xaml.Shapes;
using Windows.ApplicationModel;

namespace WordFun.Models
{
    //DataSource class for connecting and communicating with database
    public class DataSource
    {
        private static DataSource _dataSource = new DataSource();

        // All words in database
        private List<Word> _allWords; 
        public List<Word> AllWords
        {
            get { return _allWords; }
            set { _allWords = value; }
        }

        private SQLiteConnection _connection;
        public SQLiteConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        public DataSource(){ }

        //Connect to database in LocalFolder
        public static void ConnectDatabase()
        {
            var dbPath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
            _dataSource.Connection = new SQLiteConnection(dbPath);

            _dataSource.Connection.CreateTable<Word>();
            _dataSource.Connection.GetMapping<Word>().PK = _dataSource.Connection.GetMapping<Word>().Columns.First();
        }

        //Get all words in database
        public static List<Word> GetAllWords() 
        {
            _dataSource._allWords = new List<Word>();
            _dataSource._allWords = _dataSource.Connection.Table<Word>().ToList();
            return _dataSource.AllWords;
        }

        //Get a word with id
        public static Word GetWordById(int id) 
        {
            if (_dataSource.AllWords == null)
                GetAllWords();

            var matches = _dataSource.AllWords.Where(w => w.Id == id).FirstOrDefault();
            return matches;
        }

        //Get a word with content
        public static Word GetWordByContent(string content) 
        {
            if (_dataSource.AllWords == null)
                GetAllWords();

            var match = _dataSource.AllWords.Where(w => w.Content == content).FirstOrDefault();
            return match;
        }

        //Get a list word which parameter contains in word's Content
        public static List<Word> GetWordsByContent(string content) 
        {
            if (_dataSource.AllWords == null)
                GetAllWords();

            var matches = _dataSource.AllWords.Where(w => w.Content.ToLower().Contains(content.ToLower())).ToList();
            return matches;
        }

        //Get a list number of word with length
        public static List<Word> GetWordsByLenght(int length, int number) 
        {
            if (_dataSource.AllWords == null)
                GetAllWords();

            var matches = _dataSource.AllWords.Where(w => w.Content.Length == length).ToList();
            matches = matches.Take(number).ToList();
            return matches;
        }

        //Add a new word to database
        //Return: 0. word exists, 1. added
        public static int AddWord(int id, string content, string meaning, int canEdit)
        {
            Word word = GetWordById(id);
            if (word != null)
                return 0;

            word = new Word() { Id = id, Content = content, Meaning = meaning, CanEdit = canEdit };
            _dataSource.AllWords.Add(word);
            _dataSource.Connection.Insert(word);
            return 1;
        }

        //Edit a word in database 
        //Return: 0. word not exists, 1. editted, -1. can't edit
        public static int EditWord(int id, string content, string meaning, int canEdit)
        {
            Word word = GetWordById(id);
            if (word == null)
                return 0;
            else if (word.CanEdit == 0)
                return -1;

            int index = _dataSource.AllWords.IndexOf(word);
            word.Content = content;
            word.Meaning = meaning;
            word.CanEdit = canEdit;
            _dataSource.AllWords.RemoveAt(index);

            _dataSource.AllWords.Insert(index, word);
            _dataSource.Connection.Update(word);
            return 1;
        }

        //Delete a word in database
        //Return: 0. word not exists, 1. deleted, -1. can't delete
        public static int RemoveWord(int id)
        {
            Word word = GetWordById(id);
            if (word == null)
                return 0;
            else if (word.CanEdit == 0)
                return -1;

            int index = _dataSource.AllWords.IndexOf(word);
            _dataSource.AllWords.RemoveAt(index);
            _dataSource.Connection.Delete(word);
            return 1;
        }
    }
}
